using System;
using System.Diagnostics;
using System.IO;
using System.Collections.Generic;
using System.Data.OleDb;
using BIUtilities.Publish.ExcelDataObjects;


namespace BIUtilities.Publish
{
    /// <summary>
    /// Deployment tool for office 2007 documents
    /// </summary>
    public class OfficeFileDeployUtility : LocalWebFileEdit
    {
        /// <summary>
        /// Update data odc connection locations within the office file.
        /// </summary>
        /// <param name="connectionName"></param>
        /// <param name="newOdcLocation"></param>
        public void UpdateDataConnection(string connectionName, string newOdcLocation)
        {
            if (!File.Exists(this.LocalTempFile))
            {
                throw new Exception("File has not been loaded locally, or else it has already been updated. Please Load the file first.");
            }
            PackageManager packageManager = new PackageManager(this.LocalTempFile);
            packageManager.SetDataConnectionProperties(connectionName, newOdcLocation);
            packageManager.ClosePackage();
        }

        /// <summary>
        /// If a worksheet has PAS links, then update the sheet with key PAS report properties
        /// </summary>
        /// <param name="pasServerName">Name of the server PAS properites will be read from</param>
        /// <param name="sheetName">Name of the sheet to find PAS mapping columns</param>
        /// <param name="columnPasPath">Name of the column containing a path to a report in the format: [Library Name]\[Book name]\[Page Name]</param>
        /// <param name="columnPasLibraryId">Name of the column which will be updated with the PAS library id</param>
        /// <param name="columnPasBookId">Name of the column which will be updated with the PAS book id</param>
        /// <param name="columnPasPageId">Name of the column which will be updated with the PAS page id</param>
        /// <param name="columnPasServer">Name of the column which will be updated with the PAS target server</param>
        public void UpdatePasLookups(
            string pasServerName, 
            string sheetName, 
            string columnPasPath, 
            string columnPasLibraryId, 
            string columnPasBookId, 
            string columnPasPageId, 
            string columnPasServer)
        {
            // Load Data
            PasLinkData pasLinkData = LoadExcelPasData(sheetName, columnPasPath);
            foreach (PasLinkData.PasReferenceLinksRow row in pasLinkData.PasReferenceLinks.Rows)
            {
                string[] path = row.PasPath.Split('\\');
                if (path.Length != 3)
                {
                    throw new Exception("Path is invalid, needs to be three parts separated by slashes indicating path to report. Error with " + path);
                }

                ProclarityAnalyticServerDeployUtility pasDeploy = new ProclarityAnalyticServerDeployUtility();
                pasDeploy.TargetWebserviceUrl = pasServerName;
                pasDeploy.MatchOnLibrary = path[0];
                pasDeploy.MatchOnBook = path[1];
                pasDeploy.MatchOnPage = path[2];
                pasDeploy.LoadPasMatchedItems();
                PasReportDetails reportDetails = new PasReportDetails();
                if (pasDeploy.FindReport(row.PasPath, out reportDetails))
                {
                    row.LibraryId = reportDetails.Library.ID.ToString("B");
                    row.BookId = reportDetails.Book.ID.ToString("B");
                    row.PageId = reportDetails.BookElement.ID.ToString("B");
                    Trace.WriteLine("Updating Excel Pas Lookup Refence for: " + row.PasPath);
                }
                else
                {
                    row.LibraryId = "NOTFOUND";
                    row.BookId = "NOTFOUND";
                    row.PageId = "NOTFOUND";
                    Trace.WriteLine("Pas Path in Excel Lookup is not found for: " + row.PasPath);
                }
                row.ServerName = pasServerName;
                
                // Update the changed row in the excel file
                UpdateExcelPasData(
                    sheetName, columnPasPath, columnPasLibraryId, columnPasBookId, columnPasPageId, columnPasServer, row);

            }
        }

        /// <summary>
        /// Load the sheet of information from the excel file.
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="columnPasPath"></param>
        /// <returns></returns>
        private PasLinkData LoadExcelPasData(string sheetName, string columnPasPath)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=" + this.LocalTempFile + @";Extended Properties=""Excel 12.0;HDR=YES""";
            PasLinkData pasLinkData = new PasLinkData();

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand(
                    "SELECT [" + columnPasPath + "] AS PasPath FROM [" + sheetName + "$]", conn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(pasLinkData, pasLinkData.PasReferenceLinks.TableName);
            }
            return pasLinkData;
        }


        /// <summary>
        /// Update a row of data in the excel sheet with pas report information.
        /// </summary>
        /// <param name="sheetName">Name of the sheet to find PAS mapping columns</param>
        /// <param name="columnPasPath">Name of the column containing a path to a report in the format: [Library Name]\[Book name]\[Page Name]</param>
        /// <param name="columnPasLibraryId">Name of the column which will be updated with the PAS library id</param>
        /// <param name="columnPasBookId">Name of the column which will be updated with the PAS book id</param>
        /// <param name="columnPasPageId">Name of the column which will be updated with the PAS page id</param>
        /// <param name="columnPasServer">Name of the column which will be updated with the PAS target server</param>
        /// <param name="row">Data to inject in the excel row updated</param>
        private void UpdateExcelPasData(
            string sheetName, string columnPasPath, string columnPasLibraryId, 
            string columnPasBookId, string columnPasPageId, string columnPasServer, 
            PasLinkData.PasReferenceLinksRow row)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=" + this.LocalTempFile + @";Extended Properties=""Excel 12.0;HDR=YES""";
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(
                    "UPDATE [" + sheetName + "$] SET " +
                    "[" + columnPasLibraryId + @"] = ?," +
                    "[" + columnPasBookId + @"] = ?," +
                    "[" + columnPasPageId + @"] = ?," +
                    "[" + columnPasServer + @"] = ? " +
                    "WHERE [" + columnPasPath + "] = ?", conn);
                cmd.Parameters.AddWithValue("LibraryId", row.LibraryId);
                cmd.Parameters.AddWithValue("BookId", row.BookId);
                cmd.Parameters.AddWithValue("PageId", row.PageId);
                cmd.Parameters.AddWithValue("ServerName", row.ServerName);
                cmd.Parameters.AddWithValue("PasPath", row.PasPath);
                cmd.ExecuteNonQuery();
            }
        }


    }
}
